﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

using EntitiesLayer.Entities;
using System.Data.OracleClient;
using System.Data;
using Oracle.DataAccess;

namespace DataLayer.DAO
{
    public class CombosDao
    {
        private Connection.Connection cn;
        private OracleConnection objCn;

        public CombosDao()
        {
            cn = new Connection.Connection();
            objCn = cn.Conecta();
        }

        public List<Combos> getAllComboRelaPaci()
        {
            List<Combos> list = new List<Combos>();

            using (OracleCommand cmd = new OracleCommand("ComboRelacionPaciente", objCn))
            {
                cmd.CommandType = CommandType.StoredProcedure;
                OracleParameter refparameter = new OracleParameter();
                refparameter.ParameterName = "RECORDSET";
                refparameter.OracleType = OracleType.Cursor;
                refparameter.Direction = ParameterDirection.Output;
                cmd.Parameters.Add(refparameter);

                try
                {
                    objCn.Open();

                    OracleDataReader dr = cmd.ExecuteReader();
           
                    while (dr.Read())
                    {
                        Combos p = new Combos();

                        p.CodRelacPaci = Convert.ToString(dr[0]);
                        p.DesRelacPaci = Convert.ToString(dr[1]);

                        list.Add(p);
                    }
                }
                catch (Exception ex)
                {
                    objCn.Dispose();
                }
                finally
                {
                    objCn.Close();
                }
            }
            return list;
        }


        public List<Combos> getAllComboSituacionResultado()
        {
            List<Combos> list = new List<Combos>();

            using (OracleCommand cmd = new OracleCommand("COMBOSITUACIONRESULTADO", objCn))
            {
                cmd.CommandType = CommandType.StoredProcedure;
                OracleParameter refparameter = new OracleParameter();
                refparameter.ParameterName = "RECORDSET";
                refparameter.OracleType = OracleType.Cursor;
                refparameter.Direction = ParameterDirection.Output;
                cmd.Parameters.Add(refparameter);

                try
                {
                    objCn.Open();

                    OracleDataReader dr = cmd.ExecuteReader();
       
                    while (dr.Read())
                    {
                        Combos p = new Combos();

                        p.CodSituacionResultado = Convert.ToString(dr[0]);
                        p.DesSituacionResultado = Convert.ToString(dr[1]);

                        list.Add(p);
                    }
                }
                catch (Exception ex)
                {
                    objCn.Dispose();
                }
                finally
                {
                    objCn.Close();
                }
            }
            return list;
        }

        public List<Combos> getAllComboEstadoAnalisisCausa()
        {
            List<Combos> list = new List<Combos>();

            using (OracleCommand cmd = new OracleCommand("ComboestadoNoConformidad", objCn))
            {
                cmd.CommandType = CommandType.StoredProcedure;
                OracleParameter refparameter = new OracleParameter();
                refparameter.ParameterName = "RECORDSET";
                refparameter.OracleType = OracleType.Cursor;
                refparameter.Direction = ParameterDirection.Output;
                cmd.Parameters.Add(refparameter);

                try
                {
                    objCn.Open();

                    OracleDataReader dr = cmd.ExecuteReader();
            
                    while (dr.Read())
                    {
                        Combos p = new Combos();

                        p.CodEstadoAnalisisCausa = Convert.ToString(dr[0]);
                        p.DesEstadoAnalisisCausa = Convert.ToString(dr[1]);

                        list.Add(p);
                    }
                }
                catch (Exception ex)
                {
                    objCn.Dispose();
                }
                finally
                {
                    objCn.Close();
                }
            }
            return list;
        }


        public List<Combos> getAllComboTipoDocum()
        {
            List<Combos> list = new List<Combos>();

            using (OracleCommand cmd = new OracleCommand("COMBOTIPODOCUMENTO", objCn))
            {
                cmd.CommandType = CommandType.StoredProcedure;
                OracleParameter refparameter = new OracleParameter();
                refparameter.ParameterName = "RECORDSET";
                refparameter.OracleType = OracleType.Cursor;
                refparameter.Direction = ParameterDirection.Output;
                cmd.Parameters.Add(refparameter);

                try
                {
                    objCn.Open();

                    OracleDataReader dr = cmd.ExecuteReader();
    
                    while (dr.Read())
                    {
                        Combos p = new Combos();

                        p.CodTipDocu = Convert.ToString(dr[0]);
                        p.DesTipoDocu = Convert.ToString(dr[1]);

                        list.Add(p);
                    }
                }
                catch (Exception ex)
                {
                    objCn.Dispose();
                }
                finally
                {
                    objCn.Close();
                }
            }
            return list;
        }


        public List<Combos> getAllComboSede()
        {
            List<Combos> list = new List<Combos>();

            using (OracleCommand cmd = new OracleCommand("COMBOSEDE", objCn))
            {
                cmd.CommandType = CommandType.StoredProcedure;
                OracleParameter refparameter = new OracleParameter();
                refparameter.ParameterName = "RECORDSET";
                refparameter.OracleType = OracleType.Cursor;
                refparameter.Direction = ParameterDirection.Output;
                cmd.Parameters.Add(refparameter);

                try
                {
                    objCn.Open();

                    OracleDataReader dr = cmd.ExecuteReader();
 
                    while (dr.Read())
                    {
                        Combos p = new Combos();

                        p.CodSede = Convert.ToString(dr[0]);
                        p.DesSede = Convert.ToString(dr[1]);

                        list.Add(p);
                    }
                }
                catch (Exception ex)
                {
                    objCn.Dispose();
                }
                finally
                {
                    objCn.Close();
                }
            }
            return list;
        }


        public List<Combos> getAllComboUniNegocio()
        {
            List<Combos> list = new List<Combos>();

            using (OracleCommand cmd = new OracleCommand("COMBOUNIDADNEGOCIO", objCn))
            {
                cmd.CommandType = CommandType.StoredProcedure;
                OracleParameter refparameter = new OracleParameter();
                refparameter.ParameterName = "RECORDSET";
                refparameter.OracleType = OracleType.Cursor;
                refparameter.Direction = ParameterDirection.Output;
                cmd.Parameters.Add(refparameter);

                try
                {
                    objCn.Open();

                    OracleDataReader dr = cmd.ExecuteReader();
           
                    while (dr.Read())
                    {
                        Combos p = new Combos();

                        p.CodUniNegocio = Convert.ToString(dr[0]);
                        p.DesUniNegocio = Convert.ToString(dr[1]);

                        list.Add(p);
                    }
                }
                catch (Exception ex)
                {
                    objCn.Dispose();
                }
                finally
                {
                    objCn.Close();
                }
            }
            return list;
        }


        public List<Combos> getAllComboArea(string codigo)
        {
            List<Combos> list = new List<Combos>();

            using (OracleCommand cmd = new OracleCommand("COMBOAREAINVOLUCRADA", objCn))
            {
                cmd.CommandType = CommandType.StoredProcedure;
                OracleParameter refparameter = new OracleParameter();
                refparameter.ParameterName = "RECORDSET";
                refparameter.OracleType = OracleType.Cursor;
                refparameter.Direction = ParameterDirection.Output;

                OracleParameter pbjp = new OracleParameter();
                pbjp.ParameterName = "RSED_COD_SEDE";
                pbjp.OracleType = OracleType.VarChar;
                pbjp.Direction = ParameterDirection.Input;
                pbjp.Value = codigo;
                cmd.Parameters.Add(refparameter);
                cmd.Parameters.Add(pbjp);                            
                try
                {
                    objCn.Open();            
                    OracleDataReader dr = cmd.ExecuteReader();                 
         
                    while (dr.Read())
                    {
                        Combos p = new Combos();
                        p.CodArea = Convert.ToString(dr[0]);
                        p.DesArea = Convert.ToString(dr[1]);
                        p.Mailjefearea = Convert.ToString(dr[2]);
                        list.Add(p);                        
                    }
                   
                }
                catch (Exception ex)
                {
                    objCn.Dispose();
                }
                finally
                {
                    objCn.Close();
                }
            }
            return list;
        }


        public List<Combos> getAllComboSeguros()
        {
            List<Combos> list = new List<Combos>();

            using (OracleCommand cmd = new OracleCommand("COMBOSEGUROS", objCn))
            {
                cmd.CommandType = CommandType.StoredProcedure;
                OracleParameter refparameter = new OracleParameter();
                refparameter.ParameterName = "RECORDSET";
                refparameter.OracleType = OracleType.Cursor;
                refparameter.Direction = ParameterDirection.Output;
                cmd.Parameters.Add(refparameter);

                try
                {
                    objCn.Open();

                    OracleDataReader dr = cmd.ExecuteReader();
  
                    while (dr.Read())
                    {
                        Combos p = new Combos();

                        p.CodSeguros = Convert.ToString(dr[0]);
                        p.DesSeguros = Convert.ToString(dr[1]);

                        list.Add(p);
                    }
                }
                catch (Exception ex)
                {
                    objCn.Dispose();
                }
                finally
                {
                    objCn.Close();
                }
            }
            return list;
        }

        public List<Combos> getAllComboTipoPaci()
        {
            List<Combos> list = new List<Combos>();

            using (OracleCommand cmd = new OracleCommand("COMBOTIPOPACIENTE", objCn))
            {
                cmd.CommandType = CommandType.StoredProcedure;
                OracleParameter refparameter = new OracleParameter();
                refparameter.ParameterName = "RECORDSET";
                refparameter.OracleType = OracleType.Cursor;
                refparameter.Direction = ParameterDirection.Output;
                cmd.Parameters.Add(refparameter);

                try
                {
                    objCn.Open();

                    OracleDataReader dr = cmd.ExecuteReader();
 
                    while (dr.Read())
                    {
                        Combos p = new Combos();

                        p.CodTipoPaci = Convert.ToString(dr[0]);
                        p.DesTipoPaci = Convert.ToString(dr[1]);

                        list.Add(p);
                    }
                }
                catch (Exception ex)
                {
                    objCn.Dispose();
                }
                finally
                {
                    objCn.Close();
                }
            }
            return list;
        }

        public List<Combos> getAllComboDpto()
        {
            List<Combos> list = new List<Combos>();

            using (OracleCommand cmd = new OracleCommand("COMBOUBIGEODPTO", objCn))
            {
                cmd.CommandType = CommandType.StoredProcedure;
                OracleParameter refparameter = new OracleParameter();
                refparameter.ParameterName = "RECORDSET";
                refparameter.OracleType = OracleType.Cursor;
                refparameter.Direction = ParameterDirection.Output;
                cmd.Parameters.Add(refparameter);

                try
                {
                    objCn.Open();

                    OracleDataReader dr = cmd.ExecuteReader();
     
                    while (dr.Read())
                    {
                        Combos p = new Combos();

                        p.CodDpto = Convert.ToString(dr[0]);
                        p.DetalleDpto = Convert.ToString(dr[1]);

                        list.Add(p);
                    }
                }
                catch (Exception ex)
                {
                    objCn.Dispose();
                }
                finally
                {
                    objCn.Close();
                }
            }
            return list;
        }

        public List<Combos> getAllComboProv(string Depto)
        {
            List<Combos> list = new List<Combos>();

            using (OracleCommand cmd = new OracleCommand("COMBOUBIGEOPROV", objCn))
            {
                cmd.CommandType = CommandType.StoredProcedure;
                OracleParameter refparameter = new OracleParameter();
                refparameter.ParameterName = "RECORDSET";
                refparameter.OracleType = OracleType.Cursor;
                refparameter.Direction = ParameterDirection.Output;
                cmd.Parameters.Add(refparameter);

                OracleParameter refdepartamento = new OracleParameter();
                refdepartamento.ParameterName = "R_DTO";
                refdepartamento.OracleType = OracleType.NVarChar;
                refdepartamento.Size = 200;
                refdepartamento.Direction = ParameterDirection.Input;
                refdepartamento.Value = Depto;
                cmd.Parameters.Add(refdepartamento);

                try
                {
                    objCn.Open();

                    OracleDataReader dr = cmd.ExecuteReader();
         
                    while (dr.Read())
                    {
                        Combos p = new Combos();

                        p.CodProv = Convert.ToString(dr[0]);
                        p.DetalleProv = Convert.ToString(dr[1]);

                        list.Add(p);
                    }
                }
                catch (Exception ex)
                {
                    objCn.Dispose();
                }
                finally
                {
                    objCn.Close();
                }
            }
            return list;
        }

        public List<Combos> getAllComboDist(string Dist)
        {
            List<Combos> list = new List<Combos>();

            using (OracleCommand cmd = new OracleCommand("COMBOUBIGEODISTRITO", objCn))
            {
                cmd.CommandType = CommandType.StoredProcedure;
                OracleParameter refparameter = new OracleParameter();
                refparameter.ParameterName = "RECORDSET";
                refparameter.OracleType = OracleType.Cursor;
                refparameter.Direction = ParameterDirection.Output;
                cmd.Parameters.Add(refparameter);

                OracleParameter refDistrito = new OracleParameter();
                refDistrito.ParameterName = "R_PROV";
                refDistrito.OracleType = OracleType.NVarChar;
                refDistrito.Size = 200;
                refDistrito.Direction = ParameterDirection.Input;
                refDistrito.Value = Dist;
                cmd.Parameters.Add(refDistrito);

                try
                {
                    objCn.Open();

                    OracleDataReader dr = cmd.ExecuteReader();
      
                    while (dr.Read())
                    {
                        Combos p = new Combos();

                        p.CodDist = Convert.ToString(dr[0]);
                        p.DetalleDist = Convert.ToString(dr[1]);

                        list.Add(p);
                    }
                }
                catch (Exception ex)
                {
                    objCn.Dispose();
                }
                finally
                {
                    objCn.Close();
                }
            }
            return list;
        }

        
       public List<Combos> getAllComboOrigen()
       {
           List<Combos> list = new List<Combos>();

           using (OracleCommand cmd = new OracleCommand("COMBOORIGEN", objCn))
           {
               cmd.CommandType = CommandType.StoredProcedure;
               OracleParameter refparameter = new OracleParameter();
               refparameter.ParameterName = "RECORDSET";
               refparameter.OracleType = OracleType.Cursor;
               refparameter.Direction = ParameterDirection.Output;
               cmd.Parameters.Add(refparameter);

               try
               {
                   objCn.Open();

                   OracleDataReader dr = cmd.ExecuteReader();
    
                   while (dr.Read())
                   {
                       Combos p = new Combos();

                       p.CodOrigen = Convert.ToString(dr[0]);
                       p.DesOrigen = Convert.ToString(dr[1]);

                       list.Add(p);
                   }
               }
               catch (Exception ex)
               {
                   objCn.Dispose();
               }
               finally
               {
                   objCn.Close();
               }
           }
           return list;
       }


       public List<Combos> getAllComboMotivoGeneral()
       {
           List<Combos> list = new List<Combos>();
           using (OracleCommand cmd = new OracleCommand("COMBOMOTIVOGENERAL", objCn))
           {
               cmd.CommandType = CommandType.StoredProcedure;
               OracleParameter refparameter = new OracleParameter();
               refparameter.ParameterName = "RECORDSET";
               refparameter.OracleType = OracleType.Cursor;
               refparameter.Direction = ParameterDirection.Output;
               cmd.Parameters.Add(refparameter);
               try
               {
                   objCn.Open();
                   OracleDataReader dr = cmd.ExecuteReader();
 
                   while (dr.Read())
                   {
                       Combos p = new Combos();
                       p.CodMotivoGeneral = Convert.ToString(dr[0]);
                       p.DesMotivoGeneral = Convert.ToString(dr[1]);
                       list.Add(p);
                   }
               }
               catch (Exception ex)
               {
                   objCn.Dispose();
               }
               finally
               {
                   objCn.Close();
               }
           }
           return list;
       }


       public List<Combos> getAllComboMotivoEspecifico(string motivoGeneral)
       {
           List<Combos> list = new List<Combos>();
           using (OracleCommand cmd = new OracleCommand("COMBOMOTIVOESPECIFICO", objCn))
           {
               cmd.CommandType = CommandType.StoredProcedure;
               OracleParameter refparameter = new OracleParameter();
               refparameter.ParameterName = "RECORDSET";
               refparameter.OracleType = OracleType.Cursor;
               refparameter.Direction = ParameterDirection.Output;
               cmd.Parameters.Add(refparameter);

               OracleParameter refDistrito = new OracleParameter();
               refDistrito.ParameterName = "R_COD_MOT_GEN";
               refDistrito.OracleType = OracleType.NVarChar;
               refDistrito.Size = 200;
               refDistrito.Direction = ParameterDirection.Input;
               refDistrito.Value = motivoGeneral;
               cmd.Parameters.Add(refDistrito);
               try
               {
                   objCn.Open();
                   OracleDataReader dr = cmd.ExecuteReader();
   
                   while (dr.Read())
                   {
                       Combos p = new Combos();
                       p.CodMotivoEspecifico = Convert.ToString(dr[0]);
                       p.DesMotivoEspecifico = Convert.ToString(dr[1]);
                       list.Add(p);
                   }
               }
               catch (Exception ex)
               {
                   objCn.Dispose();
               }
               finally
               {
                   objCn.Close();
               }
           }
           return list;
       }

       public List<Combos> getAllComboTipoSeguro()
       {
           List<Combos> list = new List<Combos>();

           using (OracleCommand cmd = new OracleCommand("COMBOTIPOSEGURO", objCn))
           {
               cmd.CommandType = CommandType.StoredProcedure;
               OracleParameter refparameter = new OracleParameter();
               refparameter.ParameterName = "RECORDSET";
               refparameter.OracleType = OracleType.Cursor;
               refparameter.Direction = ParameterDirection.Output;
               cmd.Parameters.Add(refparameter);

               try
               {
                   objCn.Open();

                   OracleDataReader dr = cmd.ExecuteReader();
  
                   while (dr.Read())
                   {
                       Combos p = new Combos();

                       p.CodTipoSeguro = Convert.ToString(dr[0]);
                       p.DesTipoSeguro = Convert.ToString(dr[1]);

                       list.Add(p);
                   }
               }
               catch (Exception ex)
               {
                   objCn.Dispose();
               }
               finally
               {
                   objCn.Close();
               }
           }
           return list;
       }

       public List<Combos> getAllComboCorreo()
       {
           List<Combos> list = new List<Combos>();

           using (OracleCommand cmd = new OracleCommand("COMBOCORREO", objCn))
           {
               cmd.CommandType = CommandType.StoredProcedure;
               OracleParameter refparameter = new OracleParameter();
               refparameter.ParameterName = "RECORDSET";
               refparameter.OracleType = OracleType.Cursor;
               refparameter.Direction = ParameterDirection.Output;
               cmd.Parameters.Add(refparameter);

               try
               {
                   objCn.Open();

                   OracleDataReader dr = cmd.ExecuteReader();
            
                   while (dr.Read())
                   {
                       Combos p = new Combos();

                       p.CodTipCorreo = Convert.ToString(dr[0]);
                       p.DesCripCorreo = Convert.ToString(dr[1]);

                       list.Add(p);
                   }
               }
               catch (Exception ex)
               {
                   objCn.Dispose();
               }
               finally
               {
                   objCn.Close();
               }
           }
           return list;
       }

        ////////////////////////////////////(JORGE)

       public List<Combos> getAllComboTipoSugerencia()
       {
           List<Combos> list = new List<Combos>();

           using (OracleCommand cmd = new OracleCommand("COMBOTIPOSUGERENCIA", objCn))
           {
               cmd.CommandType = CommandType.StoredProcedure;
               OracleParameter refparameter = new OracleParameter();
               refparameter.ParameterName = "RECORDSET";
               refparameter.OracleType = OracleType.Cursor;
               refparameter.Direction = ParameterDirection.Output;
               cmd.Parameters.Add(refparameter);

               try
               {
                   objCn.Open();

                   OracleDataReader dr = cmd.ExecuteReader();

                   while (dr.Read())
                   {
                       Combos p = new Combos();

                       p.CodTipoSugerencia = Convert.ToString(dr[0]);
                       p.DesTipoSugerencia = Convert.ToString(dr[1]);

                       list.Add(p);
                   }
               }
               catch (Exception ex)
               {
                   objCn.Dispose();
               }
               finally
               {
                   objCn.Close();
               }
           }
           return list;
       }

       public List<Combos> getAllComboProveedor()
       {
           List<Combos> list = new List<Combos>();

           using (OracleCommand cmd = new OracleCommand("COMBOPROVEEDOR", objCn))
           {
               cmd.CommandType = CommandType.StoredProcedure;
               OracleParameter refparameter = new OracleParameter();
               refparameter.ParameterName = "RECORDSET";
               refparameter.OracleType = OracleType.Cursor;
               refparameter.Direction = ParameterDirection.Output;
               cmd.Parameters.Add(refparameter);

               try
               {
                   objCn.Open();

                   OracleDataReader dr = cmd.ExecuteReader();

                   while (dr.Read())
                   {
                       Combos p = new Combos();

                       p.CodProveedor = Convert.ToString(dr[0]);
                       p.RazProveedor = Convert.ToString(dr[1]);

                       list.Add(p);
                   }
               }
               catch (Exception ex)
               {
                   objCn.Dispose();
               }
               finally
               {
                   objCn.Close();
               }
           }
           return list;
       }

       public List<Combos> getAllComboRequisito()
       {
           List<Combos> list = new List<Combos>();

           using (OracleCommand cmd = new OracleCommand("COMBOREQUISITO", objCn))
           {
               cmd.CommandType = CommandType.StoredProcedure;
               OracleParameter refparameter = new OracleParameter();
               refparameter.ParameterName = "RECORDSET";
               refparameter.OracleType = OracleType.Cursor;
               refparameter.Direction = ParameterDirection.Output;
               cmd.Parameters.Add(refparameter);

               try
               {
                   objCn.Open();

                   OracleDataReader dr = cmd.ExecuteReader();
        
                   while (dr.Read())
                   {
                       Combos p = new Combos();

                       p.CodRequisito = Convert.ToString(dr[0]);
                       p.DesRequisito = Convert.ToString(dr[1]);

                       list.Add(p);
                   }
               }
               catch (Exception ex)
               {
                   objCn.Dispose();
               }
               finally
               {
                   objCn.Close();
               }
           }
           return list;
       }

       public List<Combos> getAllComboEstadoAccion()
       {
           List<Combos> list = new List<Combos>();

           using (OracleCommand cmd = new OracleCommand("COMBOESTADOACCION", objCn))
           {
               cmd.CommandType = CommandType.StoredProcedure;
               OracleParameter refparameter = new OracleParameter();
               refparameter.ParameterName = "RECORDSET";
               refparameter.OracleType = OracleType.Cursor;
               refparameter.Direction = ParameterDirection.Output;
               cmd.Parameters.Add(refparameter);

               try
               {
                   objCn.Open();

                   OracleDataReader dr = cmd.ExecuteReader();
 
                   while (dr.Read())
                   {
                       Combos p = new Combos();

                       p.CodEstado = Convert.ToString(dr[0]);
                       p.DesEstado = Convert.ToString(dr[1]);

                       list.Add(p);
                   }
               }
               catch (Exception ex)
               {
                   objCn.Dispose();
               }
               finally
               {
                   objCn.Close();
               }
           }
           return list;
       }

       public List<Combos> getAllComboEstadoSolicitud()
       {
           List<Combos> list = new List<Combos>();

           using (OracleCommand cmd = new OracleCommand("COMBOESTADOSOLICITUD", objCn))
           {
               cmd.CommandType = CommandType.StoredProcedure;
               OracleParameter refparameter = new OracleParameter();
               refparameter.ParameterName = "RECORDSET";
               refparameter.OracleType = OracleType.Cursor;
               refparameter.Direction = ParameterDirection.Output;
               cmd.Parameters.Add(refparameter);

               try
               {
                   objCn.Open();

                   OracleDataReader dr = cmd.ExecuteReader();
         
                   while (dr.Read())
                   {
                       Combos p = new Combos();

                       p.CodEstado = Convert.ToString(dr[0]);
                       p.DesEstado = Convert.ToString(dr[1]);

                       list.Add(p);
                   }
               }
               catch (Exception ex)
               {
                   objCn.Dispose();
               }
               finally
               {
                   objCn.Close();
               }
           }
           return list;
       }

       public List<Combos> getAllComboServicio()
       {
           List<Combos> list = new List<Combos>();

           using (OracleCommand cmd = new OracleCommand("COMBOSERVICIO", objCn))
           {
               cmd.CommandType = CommandType.StoredProcedure;
               OracleParameter refparameter = new OracleParameter();
               refparameter.ParameterName = "RECORDSET";
               refparameter.OracleType = OracleType.Cursor;
               refparameter.Direction = ParameterDirection.Output;
               cmd.Parameters.Add(refparameter);

               try
               {
                   objCn.Open();

                   OracleDataReader dr = cmd.ExecuteReader();
     
                   while (dr.Read())
                   {
                       Combos p = new Combos();

                       p.CodServicio = Convert.ToString(dr[0]);
                       p.DesServicio = Convert.ToString(dr[1]);

                       list.Add(p);
                   }
               }
               catch (Exception ex)
               {
                   objCn.Dispose();
               }
               finally
               {
                   objCn.Close();
               }
           }
           return list;
       }

       public List<Combos> getAllComboPresentacion()
       {
           List<Combos> list = new List<Combos>();

           using (OracleCommand cmd = new OracleCommand("COMBOPRESENTACION", objCn))
           {
               cmd.CommandType = CommandType.StoredProcedure;
               OracleParameter refparameter = new OracleParameter();
               refparameter.ParameterName = "RECORDSET";
               refparameter.OracleType = OracleType.Cursor;
               refparameter.Direction = ParameterDirection.Output;
               cmd.Parameters.Add(refparameter);

               try
               {
                   objCn.Open();

                   OracleDataReader dr = cmd.ExecuteReader();
               
                   while (dr.Read())
                   {
                       Combos p = new Combos();

                       p.CodPresentacion = Convert.ToString(dr[0]);
                       p.DesPresentacion = Convert.ToString(dr[1]);

                       list.Add(p);
                   }
               }
               catch (Exception ex)
               {
                   objCn.Dispose();
               }
               finally
               {
                   objCn.Close();
               }
           }
           return list;
       }

       public List<Combos> getAllComboUsuario()
       {
           List<Combos> list = new List<Combos>();

           using (OracleCommand cmd = new OracleCommand("COMBOUSUARIO", objCn))
           {
               cmd.CommandType = CommandType.StoredProcedure;
               OracleParameter refparameter = new OracleParameter();
               refparameter.ParameterName = "RECORDSET";
               refparameter.OracleType = OracleType.Cursor;
               refparameter.Direction = ParameterDirection.Output;
               cmd.Parameters.Add(refparameter);

               try
               {
                   objCn.Open();

                   OracleDataReader dr = cmd.ExecuteReader();
      
                   while (dr.Read())
                   {
                       Combos p = new Combos();

                       p.CodUsuario = Convert.ToString(dr[0]);
                       p.NombresUsuario = Convert.ToString(dr[1]);

                       list.Add(p);
                   }
               }
               catch (Exception ex)
               {
                   objCn.Dispose();
               }
               finally
               {
                   objCn.Close();
               }
           }
           return list;
       }

       public List<Combos> getAllComboPerfil()
       {
           List<Combos> list = new List<Combos>();

           using (OracleCommand cmd = new OracleCommand("COMBOPERFIL", objCn))
           {
               cmd.CommandType = CommandType.StoredProcedure;
               OracleParameter refparameter = new OracleParameter();
               refparameter.ParameterName = "RECORDSET";
               refparameter.OracleType = OracleType.Cursor;
               refparameter.Direction = ParameterDirection.Output;
               cmd.Parameters.Add(refparameter);

               try
               {
                   objCn.Open();

                   OracleDataReader dr = cmd.ExecuteReader();
        
                   while (dr.Read())
                   {
                       Combos p = new Combos();

                       p.CodPerfil = Convert.ToString(dr[0]);
                       p.DesPerfil = Convert.ToString(dr[1]);

                       list.Add(p);
                   }
               }
               catch (Exception ex)
               {
                   objCn.Dispose();
               }
               finally
               {
                   objCn.Close();
               }
           }
           return list;
       }


        #region Edy

       public List<Combos> getAllComboTipoQueja()
       {
           List<Combos> list = new List<Combos>();

           using (OracleCommand cmd = new OracleCommand("COMBOTIPOQUEJA", objCn))
           {
               cmd.CommandType = CommandType.StoredProcedure;
               OracleParameter refparameter = new OracleParameter();
               refparameter.ParameterName = "RECORDSET";
               refparameter.OracleType = OracleType.Cursor;
               refparameter.Direction = ParameterDirection.Output;
               cmd.Parameters.Add(refparameter);

               try
               {
                   objCn.Open();
                   OracleDataReader dr = cmd.ExecuteReader();
                   while (dr.Read())
                   {
                       Combos p = new Combos();
                       if (dr["IQUE_PLAZO_CANTIDAD"] != DBNull.Value)
                       {
                           p.CantTipoQueja = Convert.ToInt32(dr["IQUE_PLAZO_CANTIDAD"]); 
                       }

                       p.CodTipoQueja = Convert.ToString (dr["VQUE_COD_TIP_QUE"]);
                       p.DescTipoQueja = Convert.ToString(dr["VQUE_DESC_TIP_QUE"]);
                       list.Add(p);
                   }
               }
               catch (Exception ex)
               {
                   objCn.Dispose();
                   throw ex;
               }
               finally
               {
                   objCn.Close();
               }
           }
           return list;
       }


        #endregion

    }
}
